//
var Client = require('mysql').Client,
    client = new Client(),
    TEST_DATABASE = 'rmb48_test',
    
    COLLECTION = 'Collection';
    ELEMENT = 'Element';
    SKE48 = 'Ske48';
    CONDITION = 'Conditions';
	
	COL_PROGRESS = 'CollectionProgress';
	ELE_PROGRESS = 'ElementProgress';
	CON_PROGRESS = 'ConditionProgress';
	
	USER = "User";

client.user = 'db_user';
client.password = 'db_user';

client.connect();

//DB削除
client.query('DROP DATABASE '+TEST_DATABASE, function(err) {
  if (err && err.number != Client.ERROR_DB_CREATE_EXISTS) {
    throw err;
  }
});


//DB作成
client.query('CREATE DATABASE '+TEST_DATABASE, function(err) {
  if (err && err.number != Client.ERROR_DB_CREATE_EXISTS) {
    throw err;
  }
});

//DB選択
// If no callback is provided, any errors will be emitted as `'error'`
// events by the client
client.query('USE '+TEST_DATABASE);

//テーブル作成
client.query(
  'CREATE TABLE '+ COLLECTION +
  '(id INT(11) AUTO_INCREMENT, '+
  'name TEXT, '+
  'db_name TEXT, '+ 	
  'image TEXT, '+
  'created DATETIME, '+
  'updated DATETIME, '+
  'PRIMARY KEY (id))'
);



client.query(
  'CREATE TABLE '+ ELEMENT +
  '(id INT(11) AUTO_INCREMENT, '+
  'collection_id INT(11), '+
  'name TEXT, '+ 	
  'en_name TEXT, '+ 
  'image TEXT, '+
  'created DATETIME, '+
  'updated DATETIME, '+
  'foreign key(collection_id) references ' + COLLECTION + '(id), '+
  'PRIMARY KEY (id))'
);

//SKE48
client.query(
  'CREATE TABLE '+ SKE48 +
  '(id INT(11) AUTO_INCREMENT, '+
  'nick_name TEXT, '+ 	
  'birthday TEXT, '+ 
  'bloodtype TEXT, '+   	
  'hometown TEXT, '+
  'height TEXT, '+
  'size3 TEXT, '+
  'shoes_size TEXT, '+
  'hobby TEXT, '+
  'ability TEXT, '+
  'dream TEXT, '+
  'food TEXT, '+  	
  'speech TEXT, '+  	
  'message TEXT, '+  	 	
 'url TEXT, '+  
  'created DATETIME, '+
  'updated DATETIME, '+
  'PRIMARY KEY (id))'
);

//condition
client.query(
  'CREATE TABLE '+ CONDITION +
  '(id INT(11) AUTO_INCREMENT, '+
  'element_id INT(11), '+
  'method TEXT, '+ 	
  'arg1 TEXT, '+ 
  'arg2 TEXT, '+
  'arg3 TEXT, '+
  'point TEXT, '+
  'created DATETIME, '+
  'updated DATETIME, '+
  'foreign key(element_id) references ' + ELEMENT + '(id), '+
  'PRIMARY KEY (id))'
);





//condition progress
client.query(
  'CREATE TABLE '+ CON_PROGRESS +
  '(condition_id INT(11), '+
  'user_id INT(11), '+  	
  'flag TEXT, '+
  'created DATETIME, '+
  'updated DATETIME, '+
  'foreign key(condition_id) references ' + CONDITION + '(id), '+ 	
  'foreign key(user_id) references ' + USER + '(id), '+ 	
  'PRIMARY KEY (condition_id, user_id))'
);


//element progress
client.query(
  'CREATE TABLE '+ ELE_PROGRESS +
  '(element_id INT(11), '+
  'user_id INT(11), '+  	
  'now_point INT(11), '+
  'max_point INT(11), '+
  'clear_point INT(11), '+
  'created DATETIME, '+
  'updated DATETIME, '+
  'foreign key(element_id) references ' + ELEMENT + '(id), '+ 	
  'foreign key(user_id) references ' + USER + '(id), '+ 	
  'PRIMARY KEY (element_id, user_id))'
);

//collection progress
client.query(
  'CREATE TABLE '+ COL_PROGRESS +
  '(collection_id INT(11), '+
  'user_id INT(11), '+  	
  'now_point INT(11), '+
  'max_point INT(11), '+
  'flag TEXT, '+
  'created DATETIME, '+
  'updated DATETIME, '+
  'foreign key(collection_id) references ' + COLLECTION + '(id), '+ 	
  'foreign key(user_id) references ' + USER + '(id), '+ 	
  'PRIMARY KEY (collection_id, user_id))'
);


//user
client.query(
  'CREATE TABLE '+ USER +
  '(id INT(11) AUTO_INCREMENT, '+
  'name TEXT, '+
  'twitter_id TEXT, '+ 	
  'last_tweet_id TEXT, '+ 	
  'created DATETIME, '+
  'updated DATETIME, '+
  'PRIMARY KEY (id))'
);



client.query(
  'SELECT * FROM '+COLLECTION,
  function selectCb(err, results, fields) {
    if (err) {
      throw err;
    }

    console.log(results);
 //   console.log(fields);
    client.end();
  }
);



client.query(
  'SELECT * FROM '+ELEMENT,
  function selectCb(err, results, fields) {
    if (err) {
      throw err;
    }

    console.log(results);
 //   console.log(fields);
    client.end();
  }
);


